﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NetDon.Utilities;

namespace NetDon.OA.Business.Common
{
    public class DataImportService
    {
        private const int COLUMN_RECEIVINGORDER_DATE = 16;


        private static void RemoveEmptyRows(DataTable table)
        {
            for (int i = table.Rows.Count - 1; i >= 0; i--)
            {
                DataRow row = table.Rows[i];
                if (!new Func<bool>(delegate
                                        {
                                            for (int j = 0; j < table.Columns.Count; j++)
                                            {
                                                if (!IsBlankData(row[j]))
                                                {
                                                    return true;
                                                }
                                            }
                                            return false;
                                        })())
                {
                    table.Rows.RemoveAt(i);
                }
            }
        }

        private static void RemoveEmptyColumns(DataTable table)
        {
            for (int i = table.Columns.Count - 1; i >= 0; i--)
            {
                if (!new Func<bool>(delegate
                                        {
                                            for (int j = 0; j < table.Rows.Count; j++)
                                            {
                                                if (!IsBlankData(table.Rows[j][i]))
                                                {
                                                    return true;
                                                }
                                            }
                                            return false;
                                        })())
                {
                    table.Columns.RemoveAt(i);
                }
            }
        }

        private static bool IsBlankData(object value)
        {
            if (MiscUtilities.IsNullOrEmpty(value))
            {
                return true;
            }
            if (value is string)
            {
                return string.IsNullOrEmpty(Convert.ToString(value).Trim());
            }
            return false;
        }


        /// <summary>
        /// Reads data from excel file.
        /// </summary>
        /// <param name="fileLocation">The excel file location.</param>
        /// <returns></returns>
        public static IList<IssuingOrderImportData> ReadExcelData(string fileLocation)
        {
            var data = new List<IssuingOrderImportData>();
            using (var file = new FileStream(fileLocation, FileMode.Open, FileAccess.Read))
            {
                var hssfworkbook = new HSSFWorkbook(file);
                Sheet s = hssfworkbook.GetSheetAt(0);
                int beginRow = 0;
                DateTime receivingOrderDate = DateTime.Now;
                for (int i = s.FirstRowNum; i <= s.LastRowNum; i++)
                {
                    if (s.GetRow(i).GetCell(0).StringCellValue.Contains("承运公司"))
                    {
                        string receivingOrderDateValue =
                            s.GetRow(i).GetCell(COLUMN_RECEIVINGORDER_DATE).StringCellValue.Replace("出单时间：", "").Trim();
                        if (!string.IsNullOrWhiteSpace(receivingOrderDateValue))
                        {
                            DateTime.TryParse(receivingOrderDateValue, out receivingOrderDate);
                        }
                        beginRow = i;
                        break;
                    }
                }

                for (int i = beginRow + 2; i <= s.LastRowNum; i++)
                {
                    Cell c4 = s.GetRow(i).GetCell(4); //运输计划号
                    Cell c1 = s.GetRow(i).GetCell(1); //承运货物
                    if (!string.IsNullOrWhiteSpace(c4.ToString()) && c1 != null &&!string.IsNullOrWhiteSpace(c1.ToString()))
                    {
                  
                        Cell c2 = s.GetRow(i).GetCell(2); //线路分类
                        Cell c3 = s.GetRow(i).GetCell(3); //客户编号
                      
                        Cell c5 = s.GetRow(i).GetCell(5); //交货单类型

                        Cell c6 = s.GetRow(i).GetCell(6); //交货单号
                        Cell c7 = s.GetRow(i).GetCell(7); //送货地址
                        Cell c9 = s.GetRow(i).GetCell(9); //送货省份

                        Cell c10 = s.GetRow(i).GetCell(10); //送货城市
                        Cell c11 = s.GetRow(i).GetCell(11); //开单数量
                        Cell c12 = s.GetRow(i).GetCell(12); //开单材积
                        Cell c13 = s.GetRow(i).GetCell(13); //重量KG
                        Cell c14 = s.GetRow(i).GetCell(14); //成本单价
                        Cell c17 = s.GetRow(i).GetCell(17); //结算方式

                        Cell c18 = s.GetRow(i).GetCell(18); //库存地

                        data.Add(new IssuingOrderImportData
                                     {
                                         ReceivingOrderDate = receivingOrderDate,
                                         TransportationPlanCode = c4.ToString(),
                                         DeliveryAddress = c7.ToString(),
                                         DeliveryOrderCode = c6.ToString(),
                                         DeliveryOrderType = c5.ToString(),
                                         Province = c9.ToString(),
                                         City = c10.ToString(),
                                         OrderQuantity = (int) (c11.ToString().ToDecimal()??0),
                                         OrderVolumn = c12.ToString().ToDecimal(),
                                         OrderWeight = c13.ToString().ToDecimal(),
                                         EntruckingAddress = c18.ToString(),
                                         GoodsShippedType = c1.ToString(),
                                         RouteType = c2.ToString(),
                                         CustomerId = c3.ToString(),
                                         UnitCost = c14.ToString().ToDecimal(),
                                         CheckoutType = c17.ToString()
                                     }
                            );
                    }
                }
            }
            return data;
        }

        #region Nested type: IssuingOrderImportData

        public class IssuingOrderImportData
        {
            public DateTime ReceivingOrderDate { get; set; }

            public string GoodsShippedType { get; set; }
            public string RouteType { get; set; }
            public string CustomerId { get; set; }

            public string TransportationPlanCode { get; set; }
            public string DeliveryOrderType { get; set; }
            public string DeliveryOrderCode { get; set; }
            public string Province { get; set; }
            public string City { get; set; }

            public string DeliveryAddress { get; set; }

            public string CheckoutType { get; set; }

            public string EntruckingAddress { get; set; }

            public decimal? OrderQuantity { get; set; }

            public decimal? OrderVolumn { get; set; }
            public decimal? OrderWeight { get; set; }
            public decimal? UnitCost { get; set; }
        }

        #endregion
    }
}